package com.wfs.myapp.repository.bk;

import com.wfs.myapp.entity.domain.bk.BkGroup;
import com.wfs.myapp.entity.vo.GroupListVo;
import com.wfs.myapp.entity.vo.SearchVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface GroupRepository extends JpaRepository<BkGroup, Long> {

    @Query(nativeQuery = true, value = " select count(*) from ( " +
            " SELECT g.id, g.group_name, g.group_avatar, g.description, u.id masterUserId, u.nickname masterUserName, u.avatar masterUserAvatar FROM bk_group g LEFT JOIN `user` u ON u.id = g.master_user_id WHERE g.master_user_id = :userId " +
            " union all " +
            " select gm.group_id id, g.group_name, g.group_avatar, g.description, u.id masterUserId, u.nickname masterUserName, u.avatar masterUserAvatar from bk_group_member gm left join bk_group g on gm.group_id = g.id LEFT JOIN `user` u ON u.id = g.master_user_id where gm.user_id = :userId " +
            " ) t " +
            " group by id ")
    long countByMasterUserId(Long userId);
    @Query(nativeQuery = true, value = " select id, group_name, group_avatar, description, masterUserId, masterUserName, masterUserAvatar, createdTime  from ( " +
            " SELECT g.id, g.group_name, g.group_avatar, g.description, g.created_time createdTime, u.id masterUserId, u.nickname masterUserName, u.avatar masterUserAvatar FROM bk_group g LEFT JOIN `user` u ON u.id = g.master_user_id WHERE g.master_user_id = :userId " +
            " union all " +
            " select gm.group_id id, g.group_name, g.group_avatar, g.description, gm.created_time createdTime, u.id masterUserId, u.nickname masterUserName, u.avatar masterUserAvatar from bk_group_member gm left join bk_group g on gm.group_id = g.id LEFT JOIN `user` u ON u.id = g.master_user_id where gm.user_id = :userId " +
            " ) t " +
            " group by id " +
            " order by createdTime " +
            " limit :page, :size ")
    List<GroupListVo> findByMasterUserId(Long userId, Integer page, Integer size);

    @Query(nativeQuery = true, value = " SELECT u1.id masterUserId, u1.nickname masterUserName, g.id, g.group_name, g.group_avatar, g.description FROM bk_group g " +
            " LEFT JOIN user u1 ON g.master_user_id = u1.id " +
            " WHERE 1=1 " +
            " AND IF(?1 is not null, g.id = ?1, 1=1 ) " +
            " order by g.group_name " +
            " limit ?2,?3")
    List<GroupListVo> findAllPage(Long groupId, Integer page, Integer size);
    @Query(nativeQuery = true, value = " SELECT count(*) FROM bk_group g " +
            " LEFT JOIN user u1 ON g.master_user_id = u1.id ")
    long countAllPage();

    @Query(nativeQuery = true, value =
            " SELECT 3 as type, g.id, g.group_name name, g.group_avatar image, g.description, u.id userId " +
                    "FROM bk_group g " +
                    "LEFT JOIN user u ON g.master_user_id = u.id " +
                    "WHERE g.group_name like CONCAT('%',:name,'%') "
    )
    List<SearchVo.SearchItem> findBySearchName(String name);

    @Query(nativeQuery = true, value = " SELECT g.*, u.nickname master_user_name, u.avatar master_user_avatar, count(gm.id) memberCount FROM bk_group_member gm " +
            " LEFT JOIN bk_group g ON g.id = gm.group_id " +
            " LEFT JOIN user u ON g.master_user_id = u.id " +
            " WHERE gm.user_id = ?1 " +
            " GROUP BY gm.group_id " +
            " ORDER BY gm.created_time desc "
    )
    List<GroupListVo> findByUserId(Long userId);

    @Query(nativeQuery = true, value = " SELECT * FROM bk_group_member gm " +
            " LEFT JOIN bk_group g ON g.id = gm.group_id " +
            " LEFT JOIN user u ON g.master_user_id = u.id " +
            " WHERE gm.user_id = ?1 " +
            " GROUP BY gm.group_id " +
            " ORDER BY gm.created_time desc "
    )
    List<BkGroup> findAllSaveGroup(Long userId, Long myUserId);
}

